﻿using Furion;
using Furion.DatabaseAccessor;
using Furion.DatabaseAccessor.Extensions;
using Furion.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Queer.Entity;
using Queer.IService.SystemManage;
using Queer.Model.Result.SystemManage;
using Queer.Util;
using Queer.Util.Helper;
using Queer.Util.Model;
using Queer.Model.Result;

namespace Queer.Service.SystemManage
{
    public class DatabaseTableMySqlService : IDatabaseTableService, ITransient
    {
        #region 获取数据

        public async Task<List<TableInfo>> GetTableList(string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT table_name TableName FROM information_schema.tables WHERE table_schema='" + GetDatabase() + "' AND table_type='base table'");
            IEnumerable<TableInfo> list = await strSql.ToString().SqlQueryAsync<TableInfo>();

            if (!string.IsNullOrEmpty(tableName))
                list = list.Where(p => p.TableName.Contains(tableName));

            await SetTableDetail(list);
            return list.ToList();
        }

        public async Task<List<TableInfo>> GetTablePageList(string tableName, Pagination pagination)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@"SELECT table_name TableName FROM information_schema.tables where table_schema='" + GetDatabase() + "' and (table_type='base table' or table_type='BASE TABLE')");

            if (!tableName.IsEmpty())
                strSql.Append(" AND name like @tableName");

            IEnumerable<TableInfo> list = await strSql.ToString().SqlQueryAsync<TableInfo>(new Dictionary<string, object> { { "tableName", "%" + tableName + "%" } });

            pagination.TotalCount = list.Count();
            list = list.OrderBy(a => a.TableName).Skip(pagination.PageSize * (pagination.PageIndex - 1)).Take(pagination.PageSize);

            await SetTableDetail(list);
            return list.ToList();
        }

        public async Task<List<TableFieldInfo>> GetTableFieldList(string tableName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT COLUMN_NAME TableColumn, 
		                           DATA_TYPE Datatype,
		                           (CASE COLUMN_KEY WHEN 'PRI' THEN COLUMN_NAME ELSE '' END) TableIdentity,
		                           REPLACE(REPLACE(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)),'(',''),')','') FieldLength,
	                               (CASE IS_NULLABLE WHEN 'NO' THEN 'N' ELSE 'Y' END) IsNullable,
                                   IFNULL(COLUMN_DEFAULT,'') FieldDefault,
                                   COLUMN_COMMENT Remark
                             FROM information_schema.columns WHERE table_schema='" + GetDatabase() + "' AND table_name=@TableName");

            var list = await strSql.ToString().SqlQueryAsync<TableFieldInfo>(new { TableName = tableName });
            return list;
        }

        #endregion

        #region 公有方法

        public async Task<bool> DatabaseBackup(string database, string backupPath)
        {
            string backupFile = string.Format("{0}\\{1}_{2}.bak", backupPath, database, DateTime.Now.ToString("yyyyMMddHHmmss"));
            string strSql = string.Format(" backup database [{0}] to disk = '{1}'", database, backupFile);

            var result = await strSql.SqlNonQueryAsync();
            return result > 0 ? true : false;
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 获取所有表的主键、主键名称、记录数
        /// </summary>
        /// <returns></returns>
        private async Task<List<TableInfo>> GetTableDetailList()
        {
            string strSql = @"SELECT t1.TABLE_NAME TableName,t1.TABLE_COMMENT Remark,t1.TABLE_ROWS TableCount,t2.CONSTRAINT_NAME TableKeyName,t2.column_name TableKey
                                     FROM information_schema.TABLES as t1 
	                                 LEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` as t2 on t1.TABLE_NAME = t2.TABLE_NAME
                                     WHERE t1.TABLE_SCHEMA='" + GetDatabase() + "' AND t2.TABLE_SCHEMA='" + GetDatabase() + "'";

            var list = await strSql.ToString().SqlQueryAsync<TableInfo>();
            return list;
        }

        /// <summary>
        /// 赋值表的主键、主键名称、记录数
        /// </summary>
        /// <param name="list"></param>
        private async Task SetTableDetail(IEnumerable<TableInfo> list)
        {
            List<TableInfo> detailList = await GetTableDetailList();
            foreach (TableInfo table in list)
            {
                table.TableKey = string.Join(",", detailList.Where(p => p.TableName == table.TableName).Select(p => p.TableKey));
                var tableInfo = detailList.Where(p => p.TableName == table.TableName).FirstOrDefault();
                if (tableInfo != null)
                {
                    table.TableKeyName = tableInfo.TableKeyName;
                    table.TableCount = tableInfo.TableCount;
                    table.Remark = tableInfo.Remark;
                }
            }
        }
        private string GetDatabase()
        {
            string database = HtmlHelper.Resove(App.Configuration["ConnectionStrings:DefaultConnectionString"].ToLower(), "database=", ";");
            return database;
        }

        #endregion
    }
}